<?php
class DB
{
    public static function query($conn, $sql)
    {
        if ($sql)
        {
            if ($conn instanceof PDO)
            {
                return $conn->query($sql);
            }
            else if ($conn instanceof mysqli)
            {
                return $conn->query($sql);
            }
        }
        return false;
    }
    public static function fetch($result)
    {
        if ($result instanceof PDOStatement)
        {
            return $result->fetch(PDO::FETCH_ASSOC);
        }
        else if ($result instanceof mysqli_result)
        {
            return $result->fetch_assoc();
        }
        return false;
    }
    public static function eachRow(Closure $callback, $result)
    {
        while ($row = self::fetch($result))
        {
            if (false === $callback($row))
            {
                break;
            };
        }
        if ($result instanceof mysqli_result)
        {
            $result->free();
        }
    }
    //完全不過濾,請在使用前確認參數是否安全
    //建構SQL SELECT語句
    public static function buildSelect($args, $tablename, &$offset=0, $limit=null, $orders=null)
    {
        $collection_fields = array();
        $collection_wheres = array();
        $collection_orders = array();
        foreach ((array)$args as $k=>$v)
        {
            if (is_numeric($k))
            {
                $collection_fields[] = $v;
            }
            else if (preg_match('/^[a-z_]\w*$/i', $k))
            {
                self::collect_where($collection_wheres, $k, $v);
            }
        }
        if ( ! empty($orders))
        {
            foreach ((array)$orders as $k=>$v)
            {
                $collection_orders[] = is_numeric($k)?"{$v}":"{$k} {$v}";
            }
        }
        $offset = (0 > (int)$offset)? 0 : (int)$offset;
        $limit = (0 > (int)$limit || is_null($limit))? null : (int)$limit;
        $str_fields = empty($collection_fields)?'*':join(',', $collection_fields);
        $str_wheres = self::build_where($collection_wheres);
        $str_orders = self::build_order($collection_orders);
        $str_limit = $limit?"\nLIMIT {$limit} OFFSET {$offset}":'';
        $sql = "SELECT {$str_fields} FROM {$tablename} {$str_wheres} {$str_orders} {$str_limit}";
        return $sql;
    }
    
    //建構SQL UPDATE語句
    public static function buildUpdate(array $sets, $tablename, array $wheres)
    {
        $collection_sets = array();
        $collection_wheres = array();
        foreach ($sets as $fieldname=>$field_val)
        {
            $collection_sets[] = "{$fieldname} = '{$field_val}'";
        }
        foreach ($wheres as $k=>$v)
        {
            self::collect_where($collection_wheres, $k, $v);
        }
        $str_sets = join(",\n", $collection_sets);
        $str_wheres = self::build_where($collection_wheres);
        $sql = "UPDATE {$tablename} SET \n{$str_sets} {$str_wheres}";
        return $sql;
    }
    private static function collect_where(array &$collection, $field_name, $field_val)
    {
        if (is_array($field_val))
        {
            if (preg_match('/(?:NOT\s+)?IN/i', $field_val[0]))
            {
                $collection[] = "{$field_name} {$field_val[0]} ('".join("','", (array)$field_val[1])."')";
            }
            else
            {
                $collection[] = "{$field_name} {$field_val[0]} '{$field_val[1]}'";
            }
        }
        else 
        {
            $collection[] = "{$field_name} = '{$field_val}'";
        }
    }
    private static function build_where(array $collection_wheres)
    {
        return empty($collection_wheres)?'':"\nWHERE ".join("\n    AND ", $collection_wheres);
    }
    private static function build_order(array $collection_orders)
    {
        return empty($collection_orders)?'':"\nORDER BY ".join(",\n    ", $collection_orders);
    }
}